# coding: utf8

import pymysql
import sqlite3

db_driver = sqlite3
db_name = 'testdb'


class Task63:

    def __init__(self, sqlmodule=sqlite3):
        # self.sqlmodule = sqlmodule
        if sqlmodule == pymysql:
            self.conn = pymysql.connect(
                host="localhost",
                port=3306,
                user="root",
                password="123456",
                database=db_name
            )
        if sqlmodule == sqlite3:
            self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()

    def close(self):
        self.cursor.close()
        self.conn.commit()
        self.conn.close()

    def drop_table(self, table_name):
        sql = """DROP TABLE IF EXISTS {}"""
        self.cursor.execute(sql.format(table_name))

    def create_students(self):
        # create stu_id
        table_name = 'students'
        self.drop_table(table_name)
        sql = """
            CREATE TABLE {} 
            (sno CHAR(4) PRIMARY KEY,
             name VARCHAR(20),
             age INT,
             height FLOAT,
             math FLOAT,
             squad INT
             )
            """
        cursor = self.conn.cursor()
        cursor.execute(sql.format(table_name))

    def insert_students(self):
        import faker
        fk = faker.Faker('zh_CN')
        record1 = 10

        # isnert values to stu_id
        table_name = "students"
        sql_insert = "INSERT INTO {} VALUES ".format(table_name)
        for j in range(record1):
            sql_insert += \
                "('{:04d}', '{}', {:2d}, {:.2f}, {:.2f}, {:.2f}),".format(
                    j + 1 + 1000,
                    fk.name(),
                    fk.random.randint(18, 25),          # age
                    fk.random.randint(-2, 3)/10+1.8,    # height
                    fk.random.random()*10+60,           # math
                    fk.random.randint(1, 3)             # group

                )
        sql_insert = sql_insert[:-1]
        cursor = self.conn.cursor()
        cursor.execute(sql_insert)
        cursor.close()

    def select_agg_funs(self):
        """学生人数、身高均值、年龄最大值、数学成绩总和"""
        sql = """
        SELECT squad, COUNT(*), AVG(height), MAX(age), SUM(math)
        FROM students
        WHERE age >= 20
        GROUP BY squad HAVING AVG(age) > 20
        """
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        for s in sql.split('\n'):
            print(s.lstrip())
        for row in cursor.fetchall():
            print(row)
        print("-"*60)
        cursor.close()

    def select_aggfun_with_null_valuel(self):
        """学生人数、身高均值、年龄最大值、数学成绩总和"""
        # set Null
        sql1 = """UPDATE students SET age=Null WHERE age < 21"""
        cursor = self.conn.cursor()
        cursor.execute(sql1)
        self.conn.commit()
        # sql1 = """UPDATE students SET name=Null, height=Null, math=Null WHERE sno='1003'"""
        # cursor = self.conn.cursor()
        # cursor.execute(sql1)
        # self.conn.commit()
        self.select_all_from_table('students')

        sql = """
        SELECT squad, AVG(age), COUNT(distinct height)
        FROM students
        GROUP BY squad
        """
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        for s in sql.split('\n'):
            print(s.lstrip())
        for row in cursor.fetchall():
            print(row)
        print("-"*60)
        cursor.close()

    def select_all_from_table(self, table_name):
        sql = """select * from {}""".format(table_name)
        self.cursor.execute(sql)
        self.conn.commit()
        print("[{}]".format(table_name))
        # print("-"*60)
        for row in self.cursor.fetchall():
            print(row)
        print("-"*60)

    def list_records_of_tables(self):
        for table in ["students"]:
            self.select_all_from_table(table)


if __name__ == "__main__":
    # t = Task63(sqlmodule=pymysql)
    t = Task63(sqlmodule=sqlite3)

    # t.create_students()
    # t.insert_students()

    t.select_all_from_table("students")
    t.select_agg_funs()
    t.select_aggfun_with_null_valuel()

    t.close()
